package com.ttg.common.utils.poi;

import cn.afterturn.easypoi.handler.inter.IWriter;
import com.ttg.common.enums.ErrorCodeEnums;
import com.ttg.common.exception.BaseException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.Objects;

/**
 * Description： poi导出工具类
 * Author: chenyou
 * Date: 2025/9/29 17:45
 * Company: ttg
 * Copyright: Copyright (c) 2025
 */
public class ExcelUtil {

    // 大数据量配置
    public static final int BATCH_SIZE = 1000; // 每批处理的数据量
    public static final int ROW_ACCESS_WINDOW_SIZE = 100; // SXSSF内存中保留的行数

    public static final String FILE_TYPE_XLSX = ".xlsx";

    /**
     * 创建可复用的样式对象
     */
    public static CellStyle[] createReusableStyles(Workbook workbook) {
        CellStyle[] styles = new CellStyle[4];

//        // 第一层表头样式
//        styles[0] = createHeaderStyle(workbook, IndexedColors.DARK_YELLOW.getIndex(), (short)14);
//        // 第二层表头样式
//        styles[1] = createHeaderStyle(workbook, IndexedColors.BLUE.getIndex(), (short)12);
//        // 第三层表头样式
//        styles[2] = createHeaderStyle(workbook, IndexedColors.LIGHT_BLUE.getIndex(), (short)11);
        // 第一层表头样式
        styles[0] = createHeaderStyle(workbook, (short)14);
        // 第二层表头样式
        styles[1] = createHeaderStyle(workbook, (short)12);
        // 第三层表头样式
        styles[2] = createHeaderStyle(workbook, (short)11);
        // 数据行样式
        styles[3] = createDataStyle(workbook);

        return styles;
    }

    /**
     * 设置单元格值（支持多种数据类型）
     */
    public static void setCellValue(Cell cell, Object value) {
        if (value == null) {
            cell.setCellValue("");
        } else if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Number) {
            if (value instanceof Integer || value instanceof Long) {
                cell.setCellValue(((Number) value).doubleValue());
            } else {
                cell.setCellValue(((Number) value).doubleValue());
            }
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else {
            cell.setCellValue(value.toString());
        }
    }

    /**
     * 安全的列宽自动调整方法
     */
    public static void safeAutoSizeColumns(SXSSFSheet sheet, int columnCount) {
        // 方法1: 先尝试启用列跟踪
        try {
            sheet.trackAllColumnsForAutoSizing();
        } catch (Exception e) {
            System.err.println("列跟踪启用失败: " + e.getMessage());
        }

        // 方法2: 逐列尝试自动调整，失败时使用备选方案
        for (int i = 0; i < columnCount; i++) {
            try {
                // 尝试自动调整列宽
                sheet.autoSizeColumn(i);

                // 获取调整后的列宽并设置合理范围
                int currentWidth = sheet.getColumnWidth(i);
                int adjustedWidth = getAdjustedColumnWidth(currentWidth);
                sheet.setColumnWidth(i, adjustedWidth);

                System.out.println("列 " + i + " 自动调整成功，宽度: " + adjustedWidth);

            } catch (Exception e) {
                System.err.println("列 " + i + " 自动调整失败: " + e.getMessage());

                // 使用备选方案：手动计算列宽
                int manualWidth = calculateManualColumnWidth(sheet, i);
                sheet.setColumnWidth(i, manualWidth);
                System.out.println("列 " + i + " 使用手动计算宽度: " + manualWidth);
            }
        }
    }

    /**
     * 手动计算列宽（基于内容）
     */
    private static int calculateManualColumnWidth(SXSSFSheet sheet, int columnIndex) {
        int maxWidth = 0;

        try {
            // 检查表头内容
            for (int rowNum = 0; rowNum <= 2; rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row != null) {
                    Cell cell = row.getCell(columnIndex);
                    if (cell != null) {
                        String content = getCellStringValue(cell);
                        if (content != null && !content.trim().isEmpty()) {
                            int width = calculateStringWidth(content);
                            maxWidth = Math.max(maxWidth, width);
                        }
                    }
                }
            }

            // 检查部分数据行（避免检查所有行，提高性能）
            int dataRowsToCheck = Math.min(100, Math.max(0, sheet.getLastRowNum() - 3));
            for (int rowNum = 3; rowNum <= 3 + dataRowsToCheck; rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row != null) {
                    Cell cell = row.getCell(columnIndex);
                    if (cell != null) {
                        String content = getCellStringValue(cell);
                        if (content != null && !content.trim().isEmpty()) {
                            int width = calculateStringWidth(content);
                            maxWidth = Math.max(maxWidth, width);
                        }
                    }
                }
            }

        } catch (Exception e) {
            System.err.println("手动计算列宽失败: " + e.getMessage());
        }

        // 设置合理的默认宽度范围
        return getAdjustedColumnWidth(maxWidth);
    }

    /**
     * 计算字符串的近似宽度
     */
    private static int calculateStringWidth(String content) {
        if (content == null || content.isEmpty()) {
            return 0;
        }

        // 基础宽度：每个ASCII字符约256单位
        int baseWidth = content.length() * 256;

        // 中文字符宽度调整（中文字符通常更宽）
        int chineseCharCount = countChineseCharacters(content);
        int adjustedWidth = baseWidth + (chineseCharCount * 256);

        // 数字和特殊字符的宽度调整
        if (content.matches(".*\\d+.*")) {
            adjustedWidth += 128; // 数字通常稍窄
        }

        return adjustedWidth;
    }

    /**
     * 调整列宽到合理范围
     */
    private static int getAdjustedColumnWidth(int width) {
        int minWidth = 2500;   // 约10个字符
        int maxWidth = 20000;  // 约80个字符
        int defaultWidth = 5000; // 约20个字符

        if (width <= 0) {
            return defaultWidth;
        }

        return Math.min(maxWidth, Math.max(minWidth, width + 512)); // 增加一些边距
    }

    /**
     * 统计中文字符数量
     */
    private static int countChineseCharacters(String str) {
        if (str == null) {
            return 0;
        }
        int count = 0;
        for (int i = 0; i < str.length(); i++) {
            char c = str.charAt(i);
            if (isChineseCharacter(c)) {
                count++;
            }
        }
        return count;
    }

    /**
     * 判断是否为中文字符
     */
    private static boolean isChineseCharacter(char c) {
        Character.UnicodeBlock ub = Character.UnicodeBlock.of(c);
        return ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS
                || ub == Character.UnicodeBlock.CJK_COMPATIBILITY_IDEOGRAPHS
                || ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS_EXTENSION_A
                || ub == Character.UnicodeBlock.GENERAL_PUNCTUATION
                || ub == Character.UnicodeBlock.CJK_SYMBOLS_AND_PUNCTUATION
                || ub == Character.UnicodeBlock.HALFWIDTH_AND_FULLWIDTH_FORMS;
    }


    private static boolean containsChinese(String str) {
        return str.matches(".*[\\u4e00-\\u9fa5].*");
    }

    private static String getCellStringValue(Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return null;
        }
    }

    /**
     * 创建表头样式,无背景色
     */
    public static CellStyle createHeaderStyle(Workbook workbook, short fontSize) {
        return createHeaderStyle(workbook, (short) 0,fontSize);
    }

    /**
     * 创建表头样式
     */
    public static CellStyle createHeaderStyle(Workbook workbook, short color, short fontSize) {
        CellStyle style = workbook.createCellStyle();

        // 设置背景色
        if(color!=0){
            style.setFillForegroundColor(color);
        }

        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 设置边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
//        // 设置完整的边框 - 这是关键！
//        setFullBorders(style, BorderStyle.THIN, IndexedColors.BLACK.getIndex());

        // 设置字体
        Font font = workbook.createFont();
        //加粗
//        font.setBold(true);
        font.setFontHeightInPoints(fontSize);
        if (color == IndexedColors.DARK_BLUE.getIndex()) {
            font.setColor(IndexedColors.WHITE.getIndex());
        }
        style.setFont(font);

        // 居中对齐
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        return style;
    }

    /**
     * 创建数据行样式
     */
    public static CellStyle createDataStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();

        // 设置边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        // 设置字体
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 11);
        style.setFont(font);

        return style;
    }

    /**
     * 创建合并的表头单元格
     */
    public static void createMergedHeaderCell(Sheet sheet, Row row,
                                              int firstCol, int lastCol,
                                              int firstRow, int lastRow,
                                               String value, CellStyle style) {
        // 合并单元格
        if (firstCol != lastCol || firstRow != lastRow) {
            sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
        }

        // 创建单元格并设置值和样式
        for(int i = firstCol;i<=lastCol;i++){
            Cell cell = row.createCell(i);
            cell.setCellValue(value);
            cell.setCellStyle(style);
        }

    }

    public static void workbookExport(SXSSFWorkbook workbook, HttpServletResponse response, String fileName, String fileType){
        try (ServletOutputStream outputStream = response.getOutputStream()) {
            if (workbook != null) {
                response.setCharacterEncoding("UTF-8");
                response.setHeader("content-Type", "application/vnd.ms-excel");
                response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + fileType, "UTF-8"));
                response.setHeader("Access-Control-Expose-Headers", "filename");
                response.setHeader("filename", URLEncoder.encode(fileName + fileType, "UTF-8"));
                workbook.write(outputStream);
            }
        } catch (IOException e) {
            throw new BaseException(ErrorCodeEnums.SYSTEM_ERROR.getErrcode(), "报表导出异常");
        }finally {
            workbook.dispose();
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}
